﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WaveMaster.Holidays.Club.SQL
{
    public class PassangerSQL
    {
        public static string GetAllPassangerByBookingID()
        {
            string sql = "select * from passanger WHERE m_bookingID = ?m_bookingID";
            return sql;
        }
        public static string GetAllPassangerByBookingIDAndTravelAgentID()
        {
            string sql = "select * from passanger WHERE m_bookingID = ?m_bookingID and m_travelAgentId=?m_travelAgentId order by m_boardingPassCode";
            return sql;
        }

        public static string GetPassangerByID()
        {
            string sql = "select * from passanger where m_passangerID = ?m_passangerID ";
            return sql;
        }
        public static string GetMaxPassangerID()
        {
            string sql = "select max(m_passangerID) from passanger";
            return sql;
        }
        //public static string SearchPassangerByCodeName(string p)
        //{
        //    string sql = "select * from passanger WHERE concat(m_code,m_name,m_address) like '%" + p + "%'";
        //    return sql;
        //}
        public static string DeletePassangerByID()
        {
            return @"DELETE FROM passanger
      WHERE m_passangerID = ?m_passangerID";
        }
        public static string DeletePassangerByBookingID()
        {
            return @"DELETE FROM passanger
      WHERE m_bookingID = ?m_bookingID";
        }

        public static string DeletePassangerByBookingIDAndTravelAgentID()
        {
            return @"DELETE FROM passanger
      WHERE m_bookingID = ?m_bookingID and m_travelAgentId=?m_travelAgentId";
        }
        public static string InsertPassanger()
        {
            string sql = @"INSERT INTO passanger
           (m_bookingID
           ,m_passportID
           ,m_ticketNo
            ,m_passengerStatus
            ,m_passportNo
           ,m_issueDate
           ,m_expireDate
           ,m_country
           ,m_officeIssue
           ,m_name
           ,m_gender
           ,m_birthPlace
           ,m_birthDate
           ,m_nationality
           ,m_placeOfResidence            
            ,m_occupation
            ,m_maritalStatus    
            ,m_address
            ,m_city
            ,m_zipcode
            ,m_remark
            ,m_idnumber
            ,m_boardingPassCode
            ,m_travelAgentId
            )
     VALUES
           (?m_bookingID
           ,?m_passportID
           ,?m_ticketNo
            ,?m_passengerStatus
            ,?m_passportNo
           ,?m_issueDate
           ,?m_expireDate
           ,?m_country
           ,?m_officeIssue
           ,?m_name
           ,?m_gender
           ,?m_birthPlace
           ,?m_birthDate
           ,?m_nationality
           ,?m_placeOfResidence            
            ,?m_occupation
            ,?m_maritalStatus    
            ,?m_address
            ,?m_city
            ,?m_zipcode
            ,?m_remark
            ,?m_idnumber
            ,?m_boardingPassCode
            ,?m_travelAgentId
          )";
            return sql;
        }

        public static string InsertPassangerHistory()
        {
            string sql = @"INSERT INTO passanger_history
           (m_passangerID
            ,m_bookingID
           ,m_passportID
           ,m_ticketNo
            ,m_passengerStatus
            ,m_passportNo
           ,m_issueDate
           ,m_expireDate
           ,m_country
           ,m_officeIssue
           ,m_name
           ,m_gender
           ,m_birthPlace
           ,m_birthDate
           ,m_nationality
           ,m_placeOfResidence            
            ,m_occupation
            ,m_maritalStatus    
            ,m_address
            ,m_city
            ,m_zipcode
            ,m_remark
            ,m_idnumber
            ,m_travelAgentId
            )
     VALUES
           (?m_passangerID
            ,?m_bookingID
           ,?m_passportID
           ,?m_ticketNo
            ,?m_passengerStatus
            ,?m_passportNo
           ,?m_issueDate
           ,?m_expireDate
           ,?m_country
           ,?m_officeIssue
           ,?m_name
           ,?m_gender
           ,?m_birthPlace
           ,?m_birthDate
           ,?m_nationality
           ,?m_placeOfResidence            
            ,?m_occupation
            ,?m_maritalStatus    
            ,?m_address
            ,?m_city
            ,?m_zipcode
            ,?m_remark
            ,?m_idnumber
            ,?m_travelAgentId
          )";
            return sql;
        }
        public static string UpdatePassanger()
        {
            string sql = @"UPDATE passanger
            SET m_bookingID=?m_bookingID
           ,m_passportID=?m_passportID
           ,m_ticketNo=?m_ticketNo
,m_boardingPassCode=?m_boardingPassCode
            ,m_passengerStatus = ?m_passengerStatus
            ,m_passportNo=?m_passportNo
           ,m_issueDate=?m_issueDate
           ,m_expireDate=?m_expireDate
           ,m_country=?m_country
           ,m_officeIssue=?m_officeIssue
           ,m_name=?m_name
           ,m_gender=?m_gender
           ,m_birthPlace=?m_birthPlace
           ,m_birthDate=?m_birthDate
           ,m_nationality=?m_nationality
           ,m_placeOfResidence=?m_placeOfResidence           
            ,m_occupation=?m_occupation
            ,m_maritalStatus=?m_maritalStatus    
            ,m_address=?m_address
            ,m_city=?m_city
            ,m_zipcode=?m_zipcode
            ,m_remark=?m_remark
            ,m_idnumber=?m_idnumber
            ,m_travelAgentId=?m_travelAgentId
            WHERE m_passangerID = ?m_passangerID";
            return sql;
        }
        public static string UpdatePassangerStatus()
        {
            string sql = @"UPDATE passanger
   SET m_passengerStatus = ?m_passengerStatus
 WHERE m_passangerID = ?m_passangerID";
            return sql;
        }

        public static string UpdatePassangerStatusAndBoardingPass()
        {
            string sql = @"UPDATE passanger
   SET m_passengerStatus = ?m_passengerStatus, m_boardingPassCode=?m_boardingPassCode
 WHERE m_passangerID = ?m_passangerID";
            return sql;
        }

        public static string GetPassengerByBoardingPass()
        {
            string sql = "select * from passanger where m_boardingPassCode = ?m_boardingPassCode limit 1";
            return sql;
        }

        public static string GetPassengerByBoardingPassAndTrip()
        {
            string sql = @"select * from passanger p , booking b  where 
            b.m_bookingid = p.m_bookingid
            and b.m_tripid = ?m_tripid 
            and p.m_boardingPassCode = ?m_boardingPassCode limit 1";
            return sql;
        }

        public static string GetPassengerByBoardingPassAndTravelAgent()
        {
            string sql = @"select * from passanger where m_boardingPassCode = ?m_boardingPassCode
                and m_travelAgentId = ?m_travelAgentId limit 1";
            return sql;
        }

        public static string GetPassengerByBoardingPassAndTravelAgentAndTrip()
        {
            string sql = @"select * from passanger p , booking b  where 
                b.m_bookingid = p.m_bookingid
                and p.m_boardingPassCode = ?m_boardingPassCode
                and p.m_travelAgentId = ?m_travelAgentId 
                and b.m_tripid = ?m_tripid 
                limit 1";
            return sql;
        }

        public static string GetPassengerByStatusAndTrip()
        {
            string sql = @"select p.* from passanger p, booking b where b.m_bookingid = p.m_bookingid
and b.m_tripid = ?m_tripid and m_passengerStatus = ?m_passengerStatus";
            return sql;
        }

        public static string GetPassengerCountByStatusAndTrip()
        {
            string sql = @"select count(*) from passanger p, booking b where b.m_bookingid = p.m_bookingid
and b.m_tripid = ?m_tripid and m_passengerStatus = ?m_passengerStatus";
            return sql;
        }

        public static string GetPassengerByStatusAndTripAndTravelAgent()
        {
            string sql = @"select p.* from passanger p, booking b where b.m_bookingid = p.m_bookingid
and b.m_tripid = ?m_tripid and m_passengerStatus = ?m_passengerStatus and p.m_travelAgentId=?m_travelAgentId";
            return sql;
        }

        public static string GetPassengerByStatusAndTripAndCheckInStatus()
        {
            string sql = @"select p.* from passanger p, booking b where b.m_bookingid = p.m_bookingid
and b.m_tripid = ?m_tripid and m_passengerStatus = ?m_passengerStatus and b.m_bookingStatus = ?m_bookingStatus";
            return sql;
        }

        public static string GetPassengerByStatusAndTripAndCheckInStatusAndTravelAgent()
        {
            string sql = @"select p.* from passanger p, booking b where b.m_bookingid = p.m_bookingid
and b.m_tripid = ?m_tripid and m_passengerStatus = ?m_passengerStatus and b.m_bookingStatus = ?m_bookingStatus
and p.m_travelAgentId=?m_travelAgentId";
            return sql;
        }

        public static string GetPassengerByPassportAndTrip()
        {
            string sql = @"select p.* from passanger p, booking b where b.m_bookingid = p.m_bookingid
            and b.m_tripid = ?m_tripid and p.m_passportNo = ?m_passportNo";
            return sql;
        }

        public static string GetPassengerCountByTravelAgent()
        {
            string sql = @"select count(*) from passanger p where p.m_travelAgentId = ?m_travelAgentId";
            return sql;
        }
        public static string FindTicket()
        {
            return @"SELECT count(*) FROM passanger b where m_ticketNo = ?m_ticketNo";
        }
    }
}
